package com.chinasoft.dataclean;

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

public class TrafficFunnelConversionAnalysis {

    /**漏斗转换分析；
     * 漏斗是一个营销和用户体验分析中的概念，用于描述用户从初次接触产品或服务，到最终完成目标行为的整个过程。
     * 这个漏斗过程通常分为多个阶段，每个阶段代表用户执行的一组动作。
     * 随着用户在每个阶段之间转移，用户数量通常会减少，像漏斗一样，顶部宽、底部窄。
     *
     * 用户在平台的行为路径: 浏览 -> 加购 -> 购买
     * 第一次转化 浏览 -> 加购
     * 第二次转化 加购 -> 购买
     *
     * 思路：
     * 1. 统计3个指标，浏览数，加购数，购买数
     * 2. 加购数，购买数分别跟浏览数计算得到转化率
     * 3. 返回名称，转化率，数值三个字段
     */
    public static void main(String[] args) {

        SparkSession spark = SparkSession.builder().appName("TrafficFunnelConversionAnalysis")
                .master("local[*]")
                .enableHiveSupport()
                .getOrCreate();

        Dataset<Row> ds = spark.sql("WITH t AS ( " +
                "  SELECT " +
                "    SUM(CASE WHEN type = 'pv' THEN 1 ELSE 0 END) AS pv_cnt, " +
                "    SUM(CASE WHEN type = 'cart' THEN 1 ELSE 0 END) AS cart_cnt, " +
                "    SUM(CASE WHEN type = 'buy' THEN 1 ELSE 0 END) AS buy_cnt " +
                "  FROM  " +
                "    dwd.dwd_behavior_info " +
                ") " +
                " " +
                "SELECT  '浏览数' as name,pv_cnt as value,100.00 as rate FROM t " +
                "UNION ALL " +
                "SELECT  '加购数' as name,cart_cnt as value,CAST((cart_cnt/pv_cnt)*100 AS DECIMAL(10,2)) as rate FROM t " +
                "UNION ALL " +
                "SELECT  '购买数' as name,buy_cnt as value,CAST((buy_cnt/pv_cnt)*100 AS DECIMAL(10,2)) as rate FROM t");

        ds.write()
                .format("jdbc")
                .option("url","jdbc:mysql://douni43:3306/analyze")
                .option("dbtable","traffic_funnel_conversion_analysis")
                .option("user","root")
                .option("password","123456")
                .option("driver","com.mysql.jdbc.Driver")
                .mode("overwrite")
                .save();

        spark.close();
    }
}